<?php
include_once "configuration.php";
require_once "timeattendance.php";
require_once "workHoursCalculation.php";
require_once 'PHPExcel/PHPExcel/IOFactory.php';

/*
 * 
 * 
 * Below are functions that are used for user database
 * 
 * 
 */

function getStaffName($id) {
	
	$result = mysql_query("SELECT * FROM user where id = '$id'");
	$data = mysql_fetch_assoc($result);
	return $data['name'];
}


function getStaffDesignation($id) {
	$result = mysql_query("SELECT * FROM user where id = '$id'");
	$data = mysql_fetch_assoc($result);
	return $data['designation'];
}

function changeUserPassword($id, $new_password) {

	$result = mysql_query("UPDATE user SET password = '$new_password' WHERE id = '$id'");
	return true;

}

/*
 * 
 * 
 * Below are functions that are used for time_attendance database
 * 
 * 
 */
function login($userId, $password) {
	
	$result = mysql_query("SELECT * FROM user WHERE login_name = '$userId' AND password = '$password'") or die (mysql_error());
	
	while($data = mysql_fetch_array($result)) {
		return $data;
	}
		return "";
}

function viewStaffRecord($id , $start_date, $end_date) {
	
	$result = mysql_query("SELECT * FROM time_attendance WHERE id = '$id' AND date >= '$start_date' AND date <= '$end_date' ORDER BY date") or die (mysql_error());
	
	$taRecords = array();
	
	while($data = mysql_fetch_assoc($result)) {
		
		$taRecords[] = $data;		
	}

	return $taRecords;
	
}

//insert a array of time attendace records, this will be call by the readCSBFile, after reading the CSV file, we will mass insert the time attendace record
function insertTARecords($timeAttendaceRecords) {
	
	
	$count = 0;
	$totalCount = 0;
	foreach ($timeAttendaceRecords as $record){
		if(insertTARecord($record))
			$count++;
		$totalCount ++;
	}
	
	return $count . "/". $totalCount;

}

//update the time record entry with a new one
function updateTARecord($record) {
	
	$id = $record->getId();
	$date = date("Y/m/d", $record->getDate());
	$start_time = $record->getStartTime();
	$end_time= $record->getEndTime();
	$break_start = $record->getBreakstart();
	$break_end = $record->getBreakEnd();
	$day = $record->getDay();
	$approval_status = $record->getApprovalStatus();
	$remarks = $record->getRemarks();
	
	$result = mysql_query("UPDATE time_attendance SET start_time = '$start_time', end_time = '$end_time', break_start = '$break_start', break_end = '$break_end', day = '$day', approval_status = '$approval_status', remarks = '$remarks'");
	
	return true;
}

//update the remarks field of the time attendace record only
function updateTARemarks($record) {
	$id = $record->getId();
	$date = date("Y/m/d", $record->getDate());
	$remarks = $record->getRemarks();
	
	$result = mysql_query("UPDATE time_attendance SET remarks = '$remarks' WHERE id = '$id' AND date = '$date'") or die (mysql_error());
	
	return true;
}

//inset a time attendace record into the database
function insertTARecord($record) {
	
	echo $record->__toString();
	
	$id = $record->getId();
	$date = date("Y/m/d", $record->getDate());
	$start_time = $record->getStartTime();
	$end_time= $record->getEndTime();
	$break_start = $record->getBreakstart();
	$break_end = $record->getBreakEnd();
	$day = $record->getDay();
	$approval_status = $record->getApprovalStatus();
	$work_start_time = $record->getWorkStartTime();
	$remarks = $record->getRemarks();
	
	//check database if there is duplicate entry, if yes we draw this entry out.
	$result = mysql_query("SELECT * FROM time_attendance WHERE id = '$id' AND date = '$date'");
	
	$taRecord = "";
	
	if(mysql_num_rows($result) > 0) {
		$data = mysql_fetch_assoc($result);
		$taRecord = new timeattendance($id);
		$taRecord->setDate($data['date']);
		
		if($data['start_time'] != "")
			$taRecord->insertTiming($data['start_time']);
		if($data['end_time'] != "")
			$taRecord->insertTiming($data['end_time']);
		if($data['break_start'] != "")
			$taRecord->insertTiming($data['break_start']);
		if($data['break_end'] != "")
			$taRecord->insertTiming($data['break_end']);
			
		if($record->getStartTime() != "")
			$taRecord->insertTiming($record->getStartTime());
		if($record->getEndTime() != "")
			$taRecord->insertTiming($record->getEndTime());
		if($record->getBreakStart() != "")
			$taRecord->insertTiming($record->getBreakStart());
		if($record->getBreakEnd() != "")
			$taRecord->insertTiming($record->getBreakEnd());
			
			$taRecord->compute();
			
			$ta_start_time = $taRecord->getStartTime();
			$ta_end_time = $taRecord->getEndTime();
			$ta_break_start = $taRecord->getBreakStart();
			$ta_break_end = $taRecord->getBreakEnd();
	
			$result = mysql_query("UPDATE time_attendance SET start_time = '$ta_start_time',  end_time = '$ta_end_time', break_start = '$ta_break_start', break_end = '$ta_break_end' WHERE id = '$id' AND date = '$date'") or die(mysql_error());		
	}
	else {
		$result = mysql_query("INSERT INTO time_attendance (id, date, start_time, end_time, break_start, break_end, day, approval_status, work_start_time, remarks) VALUES ('$id', '$date', '$start_time', '$end_time', '$break_start', '$break_end', '$day', '$approval_status', '$work_start_time', '$remarks')") or die(mysql_error());	
	}

	return true;
}


//check if the existing record in time attendace timing has been change by compare it to the old record
function changesToTime($record) {
	
	
	$id = $record->getId();
	$date = $record->getDate();
	
	$result = mysql_query("SELECT * FROM time_attendance WHERE id = '$id' AND date = '".date('Y-m-d', $date)."'") or die(mysql_error());
	
	$data = mysql_fetch_assoc($result);
	

	//	echo $data['start_time'];
	//	echo $record->getStartTime();
	//	echo "End Time Changes : ".$data['end_time'] != $record->getEndTime();
	//	echo "Break Start Changes :".$data['break_start'] != $record->getBreakStart();
	//	echo "Break End Changes : ".$data['break_end'] != $record->getBreakEnd();
	//	echo "Day Changes : ".$data['day'] != $record->getDay();
	
	//compare the 6 criteria, work_start_time, start_time, end_time, break_start, break_end, day of the web record with the database record. 
	//If any of these field is change, we will return true, else return false;
	return ($data['work_start_time'] != $record->getWorkStartTime() || $data['start_time'] != $record->getStartTime() || $data['end_time'] != $record->getEndTime() || $data['break_start'] != $record->getBreakStart() || $data['break_end'] != $record->getBreakEnd() || $data['day'] != $record->getDay());

}

//read the upload csv file from the uploaded directory and insert the records into database
function readCSVFile() {

	$pathToFile = 'C:/xampp/htdocs/nuskin/js/uploadify/uploads/upload.csv';
	$handle = fopen($pathToFile, "r");
	
	$data = fgetcsv($handle, 1024);//read the first line, which will be the header

	set_time_limit(0);
	$previousTARecord = null;
	$timeAttendanceRecords = array();
	$taRecord;
	$count = 0;
	
	while(!feof($handle)) { 
		$data = fgetcsv($handle, 1024);
		
		//trim all the data, juz to be safe this is no white space
		$id = trim($data[1]);
		//time data is 19:00  so we want to remove the :
		//pad the number with 0 in front to make it 4 digits, eg 0900
		$start_time = str_pad(str_replace(":", "", trim($data[3])), 4, "0", STR_PAD_LEFT);
		$end_time = str_pad(str_replace(":", "", trim($data[4])), 4, "0", STR_PAD_LEFT);
		
		
		//format the date to mm-dd-yyyy
		$date = explode("/", trim($data[5]));
		
		//if it is eof, we will not do anything
		if ($id != "") {
			$formattedDate = mktime(0, 0, 0, $date[0], $date[1], $date[2]);
			
			//else we read the first record and store it but we do not add it to the array first.
			if($count==0) {
				$previousTARecord = new timeattendance($id);
				$previousTARecord->setDate($formattedDate);
				$previousTARecord->insertTiming($start_time);
				$previousTARecord->insertTiming($end_time);
				$count++;
			}
			else {
				if($formattedDate == $previousTARecord->getDate()) {
					$previousTARecord->insertTiming($start_time);
					$previousTARecord->insertTiming($end_time);
					$previousTARecord->compute();
					//$previousTARecrod->setDay(determineDay($startTime));
					$timeAttendanceRecords[] = $previousTARecord;
					$count = 0;
				}
				else {
					$previousTARecord->compute();
					//$previousTARecrod->setDay(determineDay($startTime));
					$timeAttendanceRecords[] = $previousTARecord;
					$previousTARecord = new timeattendance($id);
					$previousTARecord->setDate($formattedDate);
					$previousTARecord->insertTiming($start_time);
					$previousTARecord->insertTiming($end_time);	
				}//end else;
			}
		}//end if($id != "");
		
			

	}
		//handle last column special case
		if($count!=0) {
			$previousTARecord->compute();
			$timeAttendanceRecords[] = $previousTARecord;
		}
	
	fclose($handle);
	
	
	return insertTARecords($timeAttendanceRecords);
		
	}

function exportToExcel($id, $start_date, $end_date) {
	
	$format_start_date = date("Y-m-d", $start_date);
	$format_end_date = date("Y-m-d", $end_date);
	
	
	$result = mysql_query("SELECT * FROM time_attendance WHERE id = '$id' AND date >= '$format_start_date' AND date <= '$format_end_date' ORDER BY date") or die (mysql_error());
    
	//Load Excel Workbook using PHPExcel_IOFactory::load command
	//The name of the Excel workbook to be loaded is testing.xls
	//In order for the script to successfully read testing.xls workbook, you need to
	//place testing.xls file inside the Tests folder, so that the path of this
	//workbook will be: C:xampphtdocsphpexcelTeststesting.xls
	$objPHPexcel = PHPExcel_IOFactory::load('C:\xampp\htdocs\nuskin\timeattendance\dao\TASheet.xlsx');
	
	//Prepare the 1st Excel worksheet (named as Sheet1) in the workbook for editing
	$objWorksheet = $objPHPexcel->setActiveSheetIndex(0);
	
	//set name
	$objWorksheet->getCell('B4')->setValue(getStaffName($id));
	
	//set designation
	$objWorksheet->getCell('E4')->setValue(getStaffDesignation($id));
	
	//set month
	//$objWorksheet->getCell('H4')->setValue(date("M", $start_date));
	$objWorksheet->getCell('H4')->setValue(date("M", $start_date));
	
	$initialCounter = 7;
	$totalNormalRateHr = 0; //variable to keep track of total min of normal rate OT
    $total2XRateHr = 0; //variable to keep track of total min of 2X rate OT
   
	while($data = mysql_fetch_assoc($result)) {
		//set date
		$objWorksheet->getCell("A".$initialCounter)->setValue($data['date']);
		//set remarks
		$objWorksheet->getCell("B".$initialCounter)->setValue($data['remarks']);
		//set start time
		$objWorksheet->getCell("C".$initialCounter)->setValue($data['start_time']);
		//set end time
		$objWorksheet->getCell("D".$initialCounter)->setValue($data['end_time']);
		//set break start
		$objWorksheet->getCell("E".$initialCounter)->setValue($data['break_start']);
		//set break end
		$objWorksheet->getCell("F".$initialCounter)->setValue($data['break_end']);
		//set 1.5X rate
		if($data['day'] == "NORMAL" || $record->day == "HALF DAY") {
			$OTpay = calculateOTRate($data['date'], $data['work_start_time'], $data['end_time'], $data['day']);
        	$totalNormalRateHr += $OTpay;
        	$objWorksheet->getCell("G".$initialCounter)->setValue($OTpay);
        	$objWorksheet->getCell("H".$initialCounter)->setValue('0');
        }//set 2X rate
        else if($data['day'] == "OFF"){
        	
        	$OTpay = calculate2XRate($data['work_start_time'], $data['end_time'], $data['break_start'], $data['break_end']);
        	$total2XRateHr += $OTpay;
        	$objWorksheet->getCell("G".$initialCounter)->setValue('0');
        	$objWorksheet->getCell("H".$initialCounter)->setValue($OTpay);
        }
		$initialCounter++;
	}
	
	//set total 1.5 rate hour
	$objWorksheet->getCell('G38')->setValue($totalNormalRateHr);
	
	//set total 2x rate hour
	$objWorksheet->getCell('H38')->setValue($total2XRateHr);
	

	
	

	//Now that all cell contents are edited, you are ready to save it as Excel 2002
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
	
	
	//This is the file name: write.xls
	header("Content-type: application/octet-stream");
    header("Content-Disposition: inline; filename=".$id.".xls");
	header("Pragma: no-cache");
	header("Expires: 0"); 
	
	$objWriter->save('php://output');
	/*
	
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'PDF');
$objWriter->setSheetIndex(0);
$objWriter->setPreCalculateFormulas(false);


header('Content-Type: application/pdf');
	header('Content-Disposition: attachment;filename="membership.pdf"');
	header('Cache-Control: max-age=0');
	$objWriter->save('php://output');
*/
}
	
//update the time_attendace approval_status to 1 
function updateApprovalStatus($id, $date, $status) {
	
	$result = mysql_query("UPDATE time_attendance SET approval_status = '$status' WHERE id = '$id' AND date = '$date'") or die(mysql_error());
	
	return true;

}

	
/*
 * 
 * 
 * Below are functions that are used for approval_list database
 * 
 * 
 */
	
//select a time attendance approval record from the database base on employee id and date
function viewApprovalRecord($id, $date) {
	
	$result = mysql_query("SELECT * FROM approval_list WHERE employee_id = '$id' AND date = '$date'");
	
	return $data = mysql_fetch_assoc($result);
	
}

//update the approval status by putting in the approval personnel and datetimpstamp
function updateApproval($id, $date, $approve_by, $date_timestamp) {
	
	//update approval status bu putting in approval personnel and datetimestamp
	$result = mysql_query("UPDATE approval_list SET approve_by = '$approve_by', date_timestamp=NOW() WHERE employee_id = '$id' AND date = '$date'") or die (mysql_error());
	
	$result = mysql_query("SELECT * FROM approval_list WHERE employee_id = '$id' AND date = '$date'");
	
	//select the approval record information
	$data = mysql_fetch_assoc($result);
	
	//and update the new record also in time_attendance table
	//update the time_attendace record approva_status to 2
	//0 : Means that no approval required.
	//1: Means that there are pending approval.
	//2 : Mens that the approval request is already approval.
	$result = mysql_query("UPDATE time_attendance SET work_start_time = '".$data['work_start_time']."', start_time = '".$data['start_time']."', end_time = '".$data['end_time']."', break_start = '".$data['break_start']."', break_end = '".$data['break_end']."', day = '".$data['day']."', approval_status = 2 WHERE id = '$id' AND date = '$date'") or die (mysql_error());
	
	return true;
}

function viewApprovalDetails($id, $date) {
	
	$result = mysql_query("SELECT * FROM approval_LIST WHERE employee_id = '$id' AND date = '$date'");
	
	$data = mysql_fetch_assoc($result);
	
	return getStaffName($data['approve_by']). " on<BR>" . date("Y-m-d, H:i:s", strtotime($data['date_timestamp']));
}
	
	
//insert time attendance record into the approval table to be pending for approval
function insertApproval($record) {
	
	$id = $record->getId();
	$date = date("Y/m/d", $record->getDate());
	$start_time = $record->getStartTime();
	$end_time= $record->getEndTime();
	$break_start = $record->getBreakStart();
	$break_end = $record->getBreakEnd();
	$day = $record->getDay();
	$work_start_time = $record->getWorkStarttime();
	
	//we first check if the record is in database.
	$result = mysql_query("SELECT * FROM approval_list WHERE employee_id = '$id' AND date = '$date'");
	
	//if yes, this is a update, we just update the changes and remove the approve_by and date_timestamp
	if ($data = mysql_fetch_array($result)) {
		$result = mysql_query("UPDATE approval_list SET start_time = '$start_time', end_time = '$end_time', break_start = '$break_start', break_end = '$break_end', day = '$day', work_start_time = '$work_start_time', approve_by = NULL WHERE employee_id = '$id' AND date = '$date'") or die(mysql_error());
	}
	//else if there is a new record, we add a new entry
	else {
		$result = mysql_query("INSERT INTO approval_list (employee_id, date, start_time, end_time, break_start, break_end, day, work_start_time) VALUES ('$id', '$date', '$start_time', '$end_time', '$break_start', '$break_end', '$day', '$work_start_time')") or die(mysql_error());
	}
	
	updateApprovalStatus($id, $date, 1);
}

?>